﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class WorkItemBS : BusinessBase
    {
        public DataTable LoadWorkItemFromDB(Guid pProjectID, Guid pUseCaseID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           WorkItemID
                                       , MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , UseCaseID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID
                            ),

                            WORK_ITEMS AS 
                            (
                             SELECT
                                W.[WorkItemID]
                                  ,W.[SiraNo]
                                  ,W.[Name]
                                  , CONVERT(VARCHAR, W.[StartDate], 104) AS StartDate
                                  , CONVERT(VARCHAR, W.[EndDate], 104) AS EndDate
                                  ,W.[Resources]
                                  ,W.[Predecessor]
                                  ,W.[Percentage]
                                  , (CASE WHEN M.[Order] IS NULL AND EX.OrderSign IS NOT NULL THEN '*' ELSE M.[Order] END) AS MssSiraNo
                                  , EX.OrderSign
                                  , EX.Name AS ExtensionName
                                  , ES.SiraNo AS StepNo
                                  , PE.MssID
                                  , PE.ExtentionID
                                  , PE.ExtentionStepID
                                  , UC.Name AS UseCaseName
                              FROM [SemiCaseDB].[dbo].[WORK_ITEM] AS W 
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON W.WorkItemID = PE.WorkItemID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[MSS] AS M ON M.MssID = PE.MssID 
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION] AS EX ON EX.ExtentionID = PE.ExtentionID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION_STEP] AS ES ON ES.StepID = PE.ExtentionStepID
                                    INNER JOIN [SemiCaseDB].[dbo].[USE_CASE] AS UC ON PE.UseCaseID = UC.UseCaseID
                                )
                                
                              SELECT 
                                *
                                , (CASE WHEN ExtentionID IS NOT NULL THEN UseCaseName + ' - ' + MssSiraNo + '.' + OrderSign + ' ' + Name 
                                        WHEN MssID IS NOT NULL THEN UseCaseName + ' - Mss'
                                        ELSE UseCaseName
                                   END) AS Relation
                              FROM WORK_ITEMS
                              ORDER BY UseCaseName, [MssSiraNo], OrderSign, SiraNo";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable LoadProjectWorkItemFromDB(Guid pProjectID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           WorkItemID
                                       , MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , UseCaseID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID
                            )

                             SELECT
                                W.[WorkItemID]
                                  ,W.[SiraNo]
                                  ,W.[Name]
                                  , CONVERT(VARCHAR, W.[StartDate], 104) AS StartDate
                                  , CONVERT(VARCHAR, W.[EndDate], 104) AS EndDate
                                  ,W.[Resources]
                                  ,W.[Predecessor]
                                  ,W.[Percentage]
                                  , (CASE WHEN M.[Order] IS NULL AND EX.OrderSign IS NOT NULL THEN '*' ELSE M.[Order] END) AS MssSiraNo
                                  , EX.OrderSign
                                  , EX.Name AS ExtensionName
                                  , ES.SiraNo AS StepNo
                                  , PE.MssID
                                  , PE.ExtentionID
                                  , PE.ExtentionStepID
                                  , UC.Name AS UseCaseName
                              FROM [SemiCaseDB].[dbo].[WORK_ITEM] AS W 
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON W.WorkItemID = PE.WorkItemID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[MSS] AS M ON M.MssID = PE.MssID 
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION] AS EX ON EX.ExtentionID = PE.ExtentionID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION_STEP] AS ES ON ES.StepID = PE.ExtentionStepID
                                    INNER JOIN [SemiCaseDB].[dbo].[USE_CASE] AS UC ON PE.UseCaseID = UC.UseCaseID
                              ORDER BY UC.Name,W.[SiraNo],M.[Order], EX.OrderSign, ES.SiraNo";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable GetWorkItemInfo(Guid pWorkItemID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , WorkItemID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE WorkItemID = @WorkItemID
                            )

                            SELECT
	                            W.[WorkItemID]
                                  ,W.[SiraNo]
                                  ,W.[Name]
                                  ,W.[StartDate]
                                  ,W.[EndDate]
                                  ,W.[Resources]
                                  ,W.[Predecessor]
                                  ,W.[Percentage]
                                , PE.MssID
                                , PE.ExtentionID
                                , PE.ExtentionStepID
                               FROM [SemiCaseDB].[dbo].[WORK_ITEM] AS W 
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON W.WorkItemID = PE.WorkItemID
                              WHERE W.WorkItemID = @WorkItemID";

            data.AddSqlParameter("WorkItemID", pWorkItemID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void CreateWorkItem(Guid pProjectID,
                                   Guid pUseCaseID,
                                    Guid? pMssID,
                                    Guid? pExtentionID,
                                    Guid? pExtentionStepID,
                                    string pSiraNo,
                                    string pName,
                                    DateTime pStartDate,
                                    DateTime pEndTime,
                                    string pResources,
                                    string pPredecessor,
                                    int pPercentage)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[WORK_ITEM]
                                   ([WorkItemID]
                                   ,[SiraNo]
                                   ,[Name]
                                   ,[StartDate]
                                   ,[EndDate]
                                   ,[Resources]
                                   ,[Predecessor]
                                   ,[Percentage])
                             VALUES 
                                   (@WorkItemID
                                   ,@SiraNo
                                   ,@Name
                                   ,@StartDate
                                   ,@EndDate
                                   ,@Resources
                                   ,@Predecessor
                                   ,@Percentage)

                                INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID]
                                       ,[ExtentionID]
                                       ,TestCaseID
                                       ,ExtentionStepID
                                       ,WorkItemID)
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID
                                       ,@ExtentionID
                                       ,NULL
                                       ,@TestCaseStepID
                                       ,@WorkItemID)";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("WorkItemID", Guid.NewGuid(), SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("MssID", pMssID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("ExtentionID", pExtentionID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("TestCaseStepID", pExtentionStepID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("SiraNo", pSiraNo, SqlDbType.VarChar);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("StartDate", pStartDate, SqlDbType.DateTime);
            data.AddSqlParameter("EndDate", pEndTime, SqlDbType.DateTime);
            data.AddSqlParameter("Resources", pResources, SqlDbType.VarChar);
            data.AddSqlParameter("Predecessor", pPredecessor, SqlDbType.VarChar);
            data.AddSqlParameter("Percentage", pPercentage, SqlDbType.Int);

            data.ExecuteStatement(sqlText);
        }

        public void UpdateWorkItem(Guid pProjectID,
                                   Guid pUseCaseID,
                                    Guid? pMssID,
                                    Guid? pExtentionID,
                                    Guid? pExtentionStepID,
                                    Guid pWorkItemID,
                                    string pName,
                                    DateTime pStartDate,
                                    DateTime pEndTime,
                                    string pResources,
                                    string pPredecessor,
                                    int pPercentage)
        {

            IData data = GetDataObject();
            string sqlText = @" DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                               WHERE WorkItemID = @WorkItemID

                           INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID]
                                       ,[ExtentionID]
                                       ,TestCaseID
                                       ,ExtentionStepID
                                       ,WorkItemID)
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID
                                       ,@ExtentionID
                                       ,NULL
                                       ,@ExtentionStepID
                                       ,@WorkItemID)

                            UPDATE [SemiCaseDB].[dbo].[WORK_ITEM]
                           SET [Name] = @Name
                              ,[StartDate] = @StartDate
                              ,[EndDate] = @EndDate
                              ,[Resources] = @Resources
                              ,[Predecessor] = @Predecessor
                              ,[Percentage] = @Percentage
                             WHERE WorkItemID = @WorkItemID";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("WorkItemID", pWorkItemID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("MssID", pMssID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("ExtentionID", pExtentionID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("ExtentionStepID", pExtentionStepID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("StartDate", pStartDate, SqlDbType.DateTime);
            data.AddSqlParameter("EndDate", pEndTime, SqlDbType.DateTime);
            data.AddSqlParameter("Resources", pResources, SqlDbType.VarChar);
            data.AddSqlParameter("Predecessor", pPredecessor, SqlDbType.VarChar);
            data.AddSqlParameter("Percentage", pPercentage, SqlDbType.Int);

            data.ExecuteStatement(sqlText);
        }

        public void DeleteWorkItem(Guid pWorkItemID)
        {
            IData data = GetDataObject();
            string sqlText = @"BEGIN TRAN;

                               DELETE FROM SemiCaseDB.dbo.WORK_ITEM
                               WHERE WorkItemID = @WorkItemID


                               DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                               WHERE WorkItemID = @WorkItemID

                               COMMIT TRAN;";

            data.AddSqlParameter("WorkItemID", pWorkItemID, SqlDbType.UniqueIdentifier);
            data.ExecuteStatement(sqlText);
        }
    }
}
